create external table jms_dm.dm_network_retention_detail_dt
(
    end_center_send_time     timestamp COMMENT '末端重心发件时间',
    final_plan_sign_date     date COMMENT '',
    final_plan_sign_time     timestamp COMMENT '规划签收时间',
    aging_sign_count_type    string COMMENT '时效签收类型',
    billcode                 string COMMENT '运单号',
    final_sign_network_code  string COMMENT '派件网点编码',
    final_sign_network_name  string COMMENT '派件网点名称',
    end_center_code          string COMMENT '转运中心/集散编码',
    end_center_name          string COMMENT '末端中心/集散名称',
    end_manage_region_code   string COMMENT '管理大区名称',
    end_manage_region_name   string COMMENT '管理大区编码',
    end_agent_code           string COMMENT '代理区编码',
    end_agent_name           string COMMENT '代理区',
    end_franchisee_code      string COMMENT '加盟商编码',
    end_franchisee_name      string COMMENT '加盟商',
    end_provider_id          string COMMENT '省份id',
    end_provider_name        string COMMENT '省份',
    end_city_id              string COMMENT '城市id',
    end_city_name            string COMMENT '城市',
    end_area_id              string COMMENT '区县id',
    end_area_name            string COMMENT '区县',
    end_network_arrival_time timestamp COMMENT '网点到件时间',
    deliver_time             timestamp COMMENT '网点出仓时间',
    collect_in_time          timestamp COMMENT '时效签收时间',
    difficult_type           string COMMENT '问题件类型',
    terminal_type            string COMMENT '对接类型',
    deliver_out_time         timestamp COMMENT '驿站/快递柜业务员取出时间',
    collect_out_time         timestamp COMMENT '出库时间',
    sign_time                timestamp COMMENT '签收时间',
    proxy_in_time            timestamp COMMENT '代理点收入时间',
    proxy_in_network_name    string COMMENT '代理点名称',
    difficult_time           timestamp COMMENT '问题件扫描时间',
    deliver_township_name    string COMMENT '出仓乡镇',
    deliver_shift            string COMMENT '散货班次',
    goods_type_code          string COMMENT '物品类型',
    order_source_name        string COMMENT '订单来源',
    order_source_code        string COMMENT '订单来源编码',
    date_time                timestamp COMMENT '分区日期',
    third_code               string COMMENT '三段码'
)
    comment '网点滞留件明细'
    PARTITIONED BY ( dt string COMMENT '时间分区')
    stored as parquet
    LOCATION '/dw/hive/jms_dm.db/external/dm_network_retention_detail_dt'
    TBLPROPERTIES ( 'discover.partitions' = 'false', 'parquet.column.index.access' = 'true'
    );
    alter table jms_dm.dm_network_retention_detail_dt add columns (
                   third_name string comment'三段码名字'
                   ) cascade ;

 create external table jms_dm.dm_network_retention_detail_real_time_dt
     (
       end_center_send_time     timestamp COMMENT '末端重心发件时间',
       final_plan_sign_date     date COMMENT '',
       final_plan_sign_time     timestamp COMMENT '规划签收时间',
       aging_sign_count_type    string COMMENT '时效签收类型',
       billcode                 string COMMENT '运单号',
       final_sign_network_code  string COMMENT '派件网点编码',
       final_sign_network_name  string COMMENT '派件网点名称',
       end_center_code          string COMMENT '转运中心/集散编码',
       end_center_name          string COMMENT '末端中心/集散名称',
       end_manage_region_code   string COMMENT '管理大区名称',
       end_manage_region_name   string COMMENT '管理大区编码',
       end_agent_code           string COMMENT '代理区编码',
       end_agent_name           string COMMENT '代理区',
       end_franchisee_code      string COMMENT '加盟商编码',
       end_franchisee_name      string COMMENT '加盟商',
       end_provider_id          string COMMENT '省份id',
       end_provider_name        string COMMENT '省份',
       end_city_id              string COMMENT '城市id',
       end_city_name            string COMMENT '城市',
       end_area_id              string COMMENT '区县id',
       end_area_name            string COMMENT '区县',
       end_network_arrival_time timestamp COMMENT '网点到件时间',
       deliver_time             timestamp COMMENT '网点出仓时间',
       collect_in_time          timestamp COMMENT '时效签收时间',
       difficult_type           string COMMENT '问题件类型',
       terminal_type            string COMMENT '对接类型',
       deliver_out_time         timestamp COMMENT '驿站/快递柜业务员取出时间',
       collect_out_time         timestamp COMMENT '出库时间',
       sign_time                timestamp COMMENT '签收时间',
       proxy_in_time            timestamp COMMENT '代理点收入时间',
       proxy_in_network_name    string COMMENT '代理点名称',
       difficult_time           timestamp COMMENT '问题件扫描时间',
       deliver_township_name    string COMMENT '出仓乡镇',
       deliver_shift            string COMMENT '散货班次',
       goods_type_code          string COMMENT '物品类型',
       order_source_name        string COMMENT '订单来源',
       order_source_code        string COMMENT '订单来源编码',
       date_time                timestamp COMMENT '分区日期',
       third_code               string COMMENT '三段码'
 )
   comment '网点滞留件明细提供刘庆验数'
   PARTITIONED BY ( dt string COMMENT '时间分区')
   stored as parquet
   LOCATION '/dw/hive/jms_dm.db/external/dm_network_retention_detail_real_time_dt'
   TBLPROPERTIES ( 'discover.partitions' = 'false', 'parquet.column.index.access' = 'true'
   );